package homework.jdbc.dao;

import homework.jdbc.domain.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2017/9/13.
 */
//操作User表的
public class UserDao {
    /**
     * 查询
     * @return
     */
    public List<User> findAll(){
        //获得数据库连接
      Connection conn = JDBCUtils.getConnection();
      //创建一个集合，用于将对象添加进行
      List<User> list = new ArrayList<>();
        ResultSet result = null;
        Statement statement = null;

      //以下执行
        try {
            statement = conn.createStatement();
            String sql = "select * from user";
            result = statement.executeQuery(sql);
            while (result.next()){
                User user = new User();
                user.setId(result.getInt(1));
                user.setUsername(result.getString(2));
                user.setPassword(result.getString(3));
                list.add(user);
            }
            return list;

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭资源
            if (result != null){
                try {
                    result.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (statement != null){
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }


        return null;
    }

     public boolean insert(User user){
        Connection conn = JDBCUtils.getConnection();
        String sql = "insert into user(id,username,password) values(?,?,?)";
        PreparedStatement ps = null;
         try {
             ps= conn.prepareStatement(sql);
             ps.setInt(1,user.getId());
             ps.setString(2,user.getUsername());
             ps.setString(3,user.getPassword());
             int num =ps.executeUpdate();

             if (num ==1){
                 return true;
             }
         } catch (SQLException e) {
             e.printStackTrace();
         }finally {
             //关闭资源（自己关闭）
             if (ps != null){
                 try {
                     ps.close();
                 } catch (SQLException e) {
                     e.printStackTrace();
                 }
             }
         }

         return false;
     }
     public boolean update(User user){
         //三个，不确定
         String sql = "update user set username=?,password=? where id=?";

         return false;
     }
     public boolean delete(int id){
        String sql = "delete from user where id =?";
         return false;
     }

     public User findById(int id){
//获得数据库连接
         Connection conn = JDBCUtils.getConnection();
        //
         User user = new User();

         ResultSet result = null;
         //Statement statement = null;
         //使用预编译的PreparedStatement
         PreparedStatement ps = null;
         //以下执行
         try {
             //编写sql语句  ？是占位符
             String sql = "select * from user where id = ?";
             //SQL语句交给ps进行预处理
             ps = conn.prepareStatement(sql);
             //设置sql语句的参数
             ps.setInt(1,id);
             //执行查询
             result = ps.executeQuery();
             //处理结果
             while (result.next()){
                 user.setId(result.getInt(1));
                 user.setUsername(result.getString(2));
                 user.setPassword(result.getString(3));
                 return user;
             }


         } catch (SQLException e) {
             e.printStackTrace();
         }finally {
             //关闭资源
             if (result != null){
                 try {
                     result.close();
                 } catch (SQLException e) {
                     e.printStackTrace();
                 }
             }

             if (ps != null){
                 try {
                     ps.close();
                 } catch (SQLException e) {
                     e.printStackTrace();
                 }
             }
          if (conn != null){
                 JDBCUtils.close();
          }
         }
         return null;

     }
}
